package db;

import bookings.CartItem;
import java.sql.*;
import java.util.*;

public class NWDB extends BaseDB{
      
    public NWDB() throws Exception
    {
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        Class.forName(driverName);
        con = DriverManager.getConnection("jdbc:sqlserver://10.10.2.148:14333;databaseName=JAVACAMP;", "DBAdmin", "1234");
    }
    public NWDB(String userName,String password) throws Exception
    {
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        Class.forName(driverName);
        con = DriverManager.getConnection("jdbc:sqlserver://10.10.2.148:14333;databaseName=JAVACAMP;", userName, password);
    }

    public void addNewMember(String accountName,byte[] accountPassword,String accountRealName
        ,String sex,String tel,String email,String county,String region,String address) throws Exception
    {
        String sql="INSERT INTO 會員(帳號,密碼,姓名,性別,電話,郵件,縣市,鄉鎮區,地址) VALUES(?,?,?,?,?,?,?,?,?)";        
        PreparedStatement cmd=con.prepareStatement(sql);        
        cmd.setString(1, accountName);
        cmd.setBytes(2, accountPassword);
        cmd.setString(3, accountRealName);
        cmd.setString(4, sex);
        cmd.setString(5, tel);
        cmd.setString(6, email);
        cmd.setString(7, county);
        cmd.setString(8, region);
        cmd.setString(9, address);
        cmd.execute();
        cmd.close();
    }
    
    public void updateNewMember(String accountName,byte[] accountPassword,String accountRealName
        ,String sex,String tel,String email,String county,String region,String address) throws Exception
    {
        String sql="UPDATE 會員 SET 帳號=?,密碼=?,姓名=?,性別=?,電話=?,郵件=?,縣市=?,鄉鎮區=?,地址=? WHERE 帳號=?";        
        PreparedStatement cmd=con.prepareStatement(sql);                
        cmd.setString(1, accountName);
        cmd.setBytes(2, accountPassword);
        cmd.setString(3, accountRealName);
        cmd.setString(4, sex);
        cmd.setString(5, tel);
        cmd.setString(6, email);
        cmd.setString(7, county);
        cmd.setString(8, region);
        cmd.setString(9, address);
        cmd.setString(10, accountName);
        cmd.execute();
        cmd.close();
    }
 
    public HashMap<String,Object> getMemberInfoByAccount(String accountName) throws Exception
    {
        String sql="SELECT 會員編號,帳號,密碼,姓名,性別,電話,郵件,縣市,鄉鎮區,地址 FROM 會員 WHERE 帳號=?";        
        PreparedStatement cmd=con.prepareStatement(sql);        
        cmd.setString(1, accountName);
        ResultSet rs=cmd.executeQuery();
        
        ArrayList<HashMap<String,Object>> result=ResultSetToList(rs);   
        return result.get(0);
    }
    
    public boolean checkAccountDuplicate(String account) throws Exception
    {
        String sql="SELECT COUNT(*) FROM 會員 WHERE 帳號=?";
        PreparedStatement cmd=con.prepareStatement(sql);
        cmd.setString(1, account);
        ResultSet rs=cmd.executeQuery();
                
        rs.next();
        int cc=rs.getInt(1);        
        cmd.close();
        
        if(cc>0)        
            return true;
        else
            return false;
    }
    
     public int getAcccountID(String account,byte[] password) throws Exception
    {
        String sql="SELECT 會員編號 FROM 會員 WHERE 帳號=? AND 密碼=?";
        PreparedStatement cmd=con.prepareStatement(sql);
        cmd.setString(1, account);
        cmd.setBytes(2, password);
        ResultSet rs=cmd.executeQuery();
                
        rs.next();
        int cc = rs.getInt(1);        
        cmd.close();
        
        return cc;
    }
       
    public boolean vaildAccountLogin(String account,byte[] password) throws Exception
    {
        String sql="SELECT COUNT(*) FROM 會員 WHERE 帳號=? AND 密碼=?";
        PreparedStatement cmd=con.prepareStatement(sql);
        cmd.setString(1, account);
        cmd.setBytes(2, password);
        ResultSet rs=cmd.executeQuery();
                
        rs.next();
        int cc=rs.getInt(1);        
        cmd.close();
        
        if(cc>0)        
            return true;
        else
            return false;
    }
    
   
    public boolean backAdminLogin(String account,String password) throws Exception
    {
        String sql="SELECT COUNT(*) FROM 管理者 WHERE 管理者帳號=? AND 管理者密碼=?";
        PreparedStatement cmd=con.prepareStatement(sql);
        cmd.setString(1, account);
        cmd.setString(2, password);
        ResultSet rs=cmd.executeQuery();
                
        rs.next();
        int cc=rs.getInt(1);        
        cmd.close();
        
        if(cc>0)        
            return true;
        else
            return false;
    }
    
    public ArrayList<HashMap<String,Object>> getCampListByDateCampID(String CampDate, String CampID ) throws Exception
    {
        String sql=" SELECT A.營地代碼,A.營地名稱,A.營地大小,case when (select COUNT(*) from 假日表 where 日期 = ? ) = 1 then A.營地假日定價 else A.營地平日定價 end as 價格 " +
                    "  ,A.營地說明  FROM 營地 AS A WHERE NOT EXISTS( SELECT B.營地代碼  FROM 訂單明細表  AS B WHERE A.營地代碼 = B.營地代碼 AND B.紮營日期 = ? ) AND A.營地代碼 like ? " ;        
        PreparedStatement cmd= con.prepareStatement(sql);     
        cmd.setString(1, CampDate);
        cmd.setString(2, CampDate);
        cmd.setString(3, CampID+"%");
        ResultSet rs=cmd.executeQuery();          
        
        ArrayList<HashMap<String,Object>> result=ResultSetToList(rs);   
        
        return result;
    }

    public int getProductCount(String date,String CampID) throws Exception
    {     
        String sql="SELECT count(*) " +
            "FROM 營地 AS a WHERE  NOT  EXISTS( SELECT 營地代碼  FROM 訂單明細表  AS b \n" +
            "WHERE a.營地代碼 = b.營地代碼 AND b.紮營日期 = ? ) AND 營地代碼 like ? ";
        PreparedStatement cmd=con.prepareStatement(sql); 
        cmd.setString(1, date);
        cmd.setString(2, CampID +"%" );
        ResultSet rs=cmd.executeQuery();
        
        rs.next();
        int count=rs.getInt(1);
        rs.close();
        cmd.close();                
        return count;
    }
    

    public HashMap<String , Object> getCampInfo(String campID) throws Exception
    {
        String sql = "SELECT *,ABC AS [假別] FROM 營地 WHERE 營地代碼 = ?";
        PreparedStatement cmd = con.prepareStatement(sql);
        cmd.setString(1, campID);
        ResultSet rs = cmd.executeQuery();
        
        ArrayList<HashMap<String,Object>> result = ResultSetToList(rs);
        return result.get(0);
    }
    
     public HashMap<String , Object> getCampInfo(String campID ,String campDate) throws Exception
    {
        String sql="{ call 營地資訊 (?,?) }";
        CallableStatement cmd = con.prepareCall(sql);
        cmd.setString(1, campID);
        cmd.setString(2, campDate);
        ResultSet rs = cmd.executeQuery();
        
        ArrayList<HashMap<String,Object>> result = ResultSetToList(rs);
        return result.get(0);
    }

    public ArrayList<HashMap<String,Object>> getInfoToBack(String id) throws Exception
    {
        String sql="SELECT 訂單序號,單據狀態,紮營者姓名,紮營者身分證號,紮營者連絡電話,銀行代碼,匯款帳號末五碼\n" +
                    "FROM 訂單主檔\n" +
                    "WHERE 訂單序號 = ?";      
        PreparedStatement cmd= con.prepareStatement(sql);     
        cmd.setString(1, id);
        ResultSet rs=cmd.executeQuery();                  
        ArrayList<HashMap<String,Object>> result=ResultSetToList(rs);   
        return result;
    }
    
    public ArrayList<HashMap<String,Object>> getbookingInfo(int memberID) throws Exception
    {
        String sql="SELECT   A.訂單序號,單據狀態,紮營者姓名,紮營者連絡電話,C.總價,匯款日期,銀行代碼,匯款帳號末五碼 \n" +
                    " FROM 訂單主檔 AS A INNER JOIN\n" +
                    " ( SELECT 訂單序號,SUM(營地價格) AS 總價 FROM  訂單明細表 GROUP BY 訂單序號 ) AS C\n" +
                    " ON A.訂單序號 = C.訂單序號\n" +
                    " WHERE A.會員編號 = ? ";
        PreparedStatement cmd= con.prepareStatement(sql);     
        cmd.setInt(1, memberID);
        ResultSet rs=cmd.executeQuery();                  
        ArrayList<HashMap<String,Object>> result=ResultSetToList(rs);   
        return result;
    }
    
    public ArrayList<HashMap<String,Object>> getbookingListInfo(String OrderID) throws Exception
    {
        String sql="SELECT A.訂單序號,A.紮營日期,A.營地代碼,C.營地名稱, D.會員編號\n" +
                    ",CASE WHEN B.日期 is null THEN '平日' ELSE '例假日' END AS 假別\n" +
                    ",CASE WHEN B.日期 is null THEN '六折' ELSE '九折' END AS 折扣\n" +
                    "FROM 訂單明細表 AS A LEFT JOIN [假日表] AS B\n" +
                    "ON a.紮營日期= b.日期 INNER JOIN 營地 AS C\n" +
                    "ON A.營地代碼 = C.營地代碼 INNER JOIN 訂單主檔 AS D\n" +
                    "ON A.訂單序號 = D.訂單序號 WHERE D.訂單序號 = ?";
        PreparedStatement cmd= con.prepareStatement(sql);     
        cmd.setString(1, OrderID);
        ResultSet rs=cmd.executeQuery();                  
        ArrayList<HashMap<String,Object>> result=ResultSetToList(rs);   
        return result;
    }    
    public void updateStateInBack(String id,String state) throws Exception
    {
        String sql="UPDATE 訂單主檔\n" +
                    "SET 單據狀態 = ?\n" +
                    "WHERE 訂單序號 = ?";      
        PreparedStatement cmd= con.prepareStatement(sql);     
        cmd.setString(1, state);
        cmd.setString(2, id);
        cmd.execute();
        cmd.close();
    }

    public void addloadging(String memberID,String checkinName ,String checkinTEL ,String checkinID,
    HashSet<bookings.CartItem> cartlist ) throws Exception
    { //http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html
      //http://www.codejava.net/java-se/jdbc/get-id-of-inserted-record-in-database
       // http://kodejava.org/how-do-i-commit-or-rollback-transaction-in-jdbc/
        try 
        {
            String sql = "INSERT INTO 訂單主檔(會員編號,單據狀態,紮營者姓名,紮營者身分證號,紮營者連絡電話,) VALUES(?,'已預約',?,?,?)";
            con.setAutoCommit(false);
            PreparedStatement cmd = con.prepareStatement(sql ,PreparedStatement.RETURN_GENERATED_KEYS );
            cmd.setString(1, memberID);
            cmd.setString(2, checkinName);
            cmd.setString(3, checkinID);
            cmd.setString(4, checkinTEL);
            cmd.execute();
            ResultSet rs = cmd.getGeneratedKeys();
            String generatedKey = "";
            if (rs.next()) 
            {
                generatedKey = rs.getString(1);
            }
            System.out.println("Inserted record's ID: " + generatedKey);
            
            sql = "INSERT INTO 訂單明細表 (訂單序號, 紮營日期,營地代碼,營地價格) VALUES ( ?,?,?,? )";
            PreparedStatement cmd2 = con.prepareStatement(sql);   
            HashSet<bookings.CartItem> cartItem = cartlist;
             //http://pclevin.blogspot.tw/2011/12/java-iterator.html
            ArrayList<bookings.CartItem> cc = new ArrayList<bookings.CartItem>(cartItem);
            for(bookings.CartItem ci : cc  )
            {
               cmd2.setString(1,generatedKey);
               cmd2.setString(2, ci.getCampDate());
               cmd2.setString(3, ci.getId());
               cmd2.setInt(4, ci.getPrice());
               cmd2.execute();
            }
           con.commit();
        }
        catch (SQLException e) 
        {
            // Rollback any database transaction due to exception occurred
            if (con != null) 
            {
                con.rollback();
                System.out.println("Transaction rollback...");
            }
        }
        finally 
        {
            if (con != null && !con.isClosed())
            {
                con.close();
            }
        }
    }
     public String addOrder(String memberID,String checkinName ,String checkinTEL,String checkinID) throws Exception
    {
        String sql = "INSERT INTO 訂單主檔 (會員編號,單據狀態,紮營者姓名,紮營者身分證號,紮營者連絡電話 ) VALUES (?,'已預約',?,?,?)";
 
        PreparedStatement cmd=con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);   
          cmd.setString(1, memberID);
          cmd.setString(2, checkinName);
          cmd.setString(3, checkinID);
          cmd.setString(4, checkinTEL);
          cmd.execute();
          ResultSet rs = cmd.getGeneratedKeys();
          String generatedKey = "";
          if (rs.next()) 
          {
              generatedKey = rs.getString(1);
          }
         System.out.println("Inserted record's ID: " + generatedKey);
          cmd.close();
          return generatedKey;
    }
     
     public void addOrderDetail(String gKey,HashSet<bookings.CartItem> cartlist) throws Exception
    {
        String sql = "INSERT INTO 訂單明細表 (訂單序號, 紮營日期,營地代碼,營地價格) VALUES ( ?,?,?,? )";
        PreparedStatement cmd2=con.prepareStatement(sql);   
        String gk = getNewOrderId() ;
        HashSet<bookings.CartItem> cartItem = cartlist;
          //http://pclevin.blogspot.tw/2011/12/java-iterator.html
          ArrayList<bookings.CartItem> cc = new ArrayList<bookings.CartItem>(cartItem);
         for( bookings.CartItem ci : cc  )
         {
            cmd2.setString(1,gk);
            cmd2.setString(2, ci.getCampDate());
            cmd2.setString(3, ci.getId());
            cmd2.setInt(4,  ci.getPrice());
            cmd2.execute();
        }
          cmd2.close();
    }
    public String getNewOrderId() throws Exception
    {
        String sql = "SELECT TOP(1) 訂單序號  FROM 訂單主檔  ORDER BY 訂單序號 DESC ";
        PreparedStatement cmd=con.prepareStatement(sql);   
        ResultSet rs=cmd.executeQuery();
        rs.next();
            String cc = rs.getString(1);        
        cmd.close();
        return cc  ;
    }
}
